package com.neusoft.neuiotms.dcom.service.hn.impl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.google.common.collect.Lists;
import com.neusoft.bizcore.auth.common.bean.UserBean;
import com.neusoft.bizcore.file.service.FileService;
import com.neusoft.bizcore.web.constant.ExceptionConstants;
import com.neusoft.bizcore.web.exception.UnitedException;
import com.neusoft.bizcore.web.support.Searchable;
import com.neusoft.bizcore.web.utils.EasyPoiStylerColorImpl;
import com.neusoft.neuiotms.common.dto.DictionaryDTO;
import com.neusoft.neuiotms.common.service.DictionaryService;
import com.neusoft.neuiotms.dcom.bean.ProjectUserBean;
import com.neusoft.neuiotms.dcom.constants.DCOMConstants;
import com.neusoft.neuiotms.dcom.converter.hn.HnInspectionHealthTaskAttachmentConverter;
import com.neusoft.neuiotms.dcom.converter.hn.HnInspectionHealthTaskConverter;
import com.neusoft.neuiotms.dcom.converter.hn.HnInspectionHealthTaskItemValueConverter;
import com.neusoft.neuiotms.dcom.dto.InstitutionsDTO;
import com.neusoft.neuiotms.dcom.dto.hn.HnInspectionHealthTaskAttachmentDTO;
import com.neusoft.neuiotms.dcom.dto.hn.HnInspectionHealthTaskDTO;
import com.neusoft.neuiotms.dcom.model.enums.HnInspectionTaskStatus;
import com.neusoft.neuiotms.dcom.model.hn.HnInspectionHealthTask;
import com.neusoft.neuiotms.dcom.model.hn.HnInspectionHealthTaskAttachment;
import com.neusoft.neuiotms.dcom.repository.hn.HnInspectionHealthTaskAttachmentRepository;
import com.neusoft.neuiotms.dcom.repository.hn.HnInspectionHealthTaskRepository;
import com.neusoft.neuiotms.dcom.service.InstitutionsService;
import com.neusoft.neuiotms.dcom.service.hn.HnInspectionHealthTaskService;
import com.neusoft.neuiotms.dcom.util.PermUtils;
import com.neusoft.neuiotms.dcom.vo.HealthInspectionTaskItemValueVO;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.word.WordExportUtil;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
public class HnInspectionHealthTaskServiceImpl implements HnInspectionHealthTaskService {

    @Autowired
    private HnInspectionHealthTaskRepository hnInspectionTaskRepository;
    @Autowired
    private HnInspectionHealthTaskConverter hnInspectionTaskConverter;
    @Autowired
    private InstitutionsService institutionsService;
    @Autowired
    private HnInspectionHealthTaskItemValueConverter itemValueConverter;
    @Autowired
    private DictionaryService dictionaryService;
    @Autowired
    private HnInspectionHealthTaskAttachmentRepository hnInspectionHealthTaskAttachmentRepository;
    @Autowired
    private HnInspectionHealthTaskAttachmentConverter hnInspectionHealthTaskAttachmentConverter;
    @Autowired
    private FileService fileService;
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<HnInspectionHealthTaskDTO> index(final Searchable searchable, final UserBean user) {
        final List<InstitutionsDTO> institutions = this.institutionsService.getPermInstitutions(user);
        final List<String> institutionCodeArray =
                institutions.stream().map(it -> it.getCode()).collect(Collectors.toList());
        searchable.put("institutionCodeArray", institutionCodeArray);
        final List<HnInspectionHealthTask> model = this.hnInspectionTaskRepository.index(searchable);
        return this.hnInspectionTaskConverter.toListDTO(model);
    }

    @Override
    public Page<HnInspectionHealthTaskDTO> search(final Pageable pageable, final Searchable searchable,
            final UserBean userBean) {
        final List<InstitutionsDTO> institutions = this.institutionsService.getPermInstitutions(userBean);
        final List<String> institutionCodeArray =
                institutions.stream().map(it -> it.getCode()).collect(Collectors.toList());
        searchable.put("institutionCodeArray", institutionCodeArray);
        final Page<HnInspectionHealthTask> page = this.hnInspectionTaskRepository.search(pageable, searchable);
        return this.hnInspectionTaskConverter.toPageDTO(page);
    }

    @Override
    public HnInspectionHealthTaskDTO update(final Long id, final HnInspectionHealthTaskDTO dto, final UserBean user) {
        final HnInspectionHealthTask model = this.hnInspectionTaskRepository.getOne(id);
        if (model == null) {
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        if (user == null) {
            throw new UnitedException("当前用户不能为空");
        }
        dto.setUserCode(user.getUsername());
        dto.setUserName(user.getName());
        dto.setStatus(HnInspectionTaskStatus.inspecting);

        return this.hnInspectionTaskConverter
                .toDTO(this.hnInspectionTaskRepository.save(this.hnInspectionTaskConverter.toModel(dto)));
    }

    @Override
    public HnInspectionHealthTaskDTO details(final Long id) {
        final HnInspectionHealthTask model = this.hnInspectionTaskRepository.getOne(id);
        if (model == null) {
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        return this.hnInspectionTaskConverter.toDTO(model);
    }

    @Override
    public void delete(final Long id) {
        final HnInspectionHealthTask model = this.hnInspectionTaskRepository.getOne(id);
        if (model == null) {
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        this.hnInspectionTaskRepository.delete(model);
    }

    @Override
    public HnInspectionHealthTaskDTO submit(final Long id, final HnInspectionHealthTaskDTO dto, final UserBean user) {
        final HnInspectionHealthTask model = this.hnInspectionTaskRepository.getOne(id);
        if (model == null) {
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        if (user == null) {
            throw new UnitedException("当前用户不能为空");
        }
        dto.setUserCode(user.getUsername());
        dto.setUserName(user.getName());
        dto.setStatus(HnInspectionTaskStatus.upload);
        dto.setActualDateTime(new Date());

        return this.hnInspectionTaskConverter
                .toDTO(this.hnInspectionTaskRepository.save(this.hnInspectionTaskConverter.toModel(dto)));
    }

    @Override
    public XWPFDocument export(final Long id) {
        if (id == null) {
            throw new UnitedException("导出定期健康巡检任务失败，必须选择一条数据");
        }
        final SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd HH:mm:ss");
        final List<DictionaryDTO> dicts =
                this.dictionaryService.findByDictionaryType(DCOMConstants.HN_INSPECTION_ITEM_TYPE);
        final Map<String, String> dictMap =
                dicts.stream().collect(Collectors.toMap(DictionaryDTO::getCode, DictionaryDTO::getText));
        final HnInspectionHealthTask model = this.hnInspectionTaskRepository.getOne(id);
        final Map<String, Object> map = new HashMap<>();
        map.put("institutionName", model.getInstitutionName());
        map.put("code", model.getCode());
        map.put("username", model.getUserName());
        map.put("startDateTime", format.format(model.getStartDateTime()));
        map.put("endDateTime", format.format(model.getEndDateTime()));
        map.put("actualDateTime", format.format(model.getActualDateTime()));
        map.put("problem", model.getProblem());
        map.put("solution", model.getSolution());
        final List<HealthInspectionTaskItemValueVO> list = new ArrayList<>();
        model.getItemValues().stream().map(t -> {
            final HealthInspectionTaskItemValueVO vo = this.itemValueConverter.toVO(t);
            if (dictMap.containsKey(t.getItem().getItemType())) {
                vo.setItemType(dictMap.get(t.getItem().getItemType()));
            }
            return vo;
        }).forEach(list::add);
        map.put("list", list);
        try {
            final XWPFDocument doc = WordExportUtil.exportWord07(
                    "word/health.docx", map);
            return doc;
        } catch (final Exception e) {
            throw new UnitedException("导出定期健康巡检任务失败");
        }
    }

    @Override
    public void deleteMongoFile(final String fsId, final Long id) {
        final List<HnInspectionHealthTaskAttachment> list =
                this.hnInspectionHealthTaskAttachmentRepository.findByFsId(fsId);
        boolean flag = false;//判断是否有其他knowledge关联同一张图片
        for (final HnInspectionHealthTaskAttachment attach : list) {
            if (attach.getTask().getId().longValue() != id) {
                flag = true;
                break;
            }
        }
        if (!flag) {
            this.fileService.deleteByFsId(fsId);
        }
    }

    //更新关联的附件
    @Override
    public void updateTaskAttachment(final Long id, final List<HnInspectionHealthTaskAttachmentDTO> attach,
            final UserBean user) {
        final HnInspectionHealthTask model = this.hnInspectionTaskRepository.getOne(id);
        //附件
        final List<HnInspectionHealthTaskAttachment> attachment =
                model.getAttachment().stream().collect(Collectors.toList());
        final List<String> fsIds =
                attach.stream().map(HnInspectionHealthTaskAttachmentDTO::getFsId).collect(Collectors.toList());
        attachment.forEach(k -> {
            if (!fsIds.contains(k.getFsId())) {
                model.getAttachment().remove(k);
                this.hnInspectionHealthTaskAttachmentRepository.delete(k);
                this.deleteMongoFile(k.getFsId(), model.getId());
            }
        });
        if (CollectionUtils.isNotEmpty(attach)) {
            attach.stream().map(t -> {
                final HnInspectionHealthTaskAttachment a =
                        this.hnInspectionHealthTaskAttachmentConverter.toModel(t);
                a.setTask(model);
                return a;
            }).forEach(model.getAttachment()::add);
        }
        model.setStatus(HnInspectionTaskStatus.finished);
        this.hnInspectionTaskRepository.save(model);
    }

    @Override
    public int getTaskCountByStatus(final UserBean user, final List<HnInspectionTaskStatus> status) {
        if (user == null) {
            throw new UnitedException("当前用户不能为空");
        }
        final ProjectUserBean projectInfo = this.institutionsService.getMyInstitutions(user);

        int count = 0;
        if (user.getRoles().stream().filter(it -> DCOMConstants.ROLE_WORKER.equals(it.getRole())).count() > 0) {
            final List<String> institutions = Lists.newArrayList(projectInfo.getDefaultInstituteCode());
            count = this.hnInspectionTaskRepository.countByInstitutionCodeInAndStatusIn(institutions,
                    status);
        } else if (user.getRoles().stream().filter(it -> DCOMConstants.ROLE_WORKER_GROUP_LEADER.equals(it.getRole()))
                .count() > 0) {
            final List<String> institutionCodeArray =
                    projectInfo.getInstitutions().stream().map(it -> it.getCode()).collect(Collectors.toList());
            count = this.hnInspectionTaskRepository.countByInstitutionCodeInAndStatusIn(institutionCodeArray,
                    status);
        } else {
            count = this.hnInspectionTaskRepository.countByStatusIn(status);
        }

        return count;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Page<Map<String, Object>> report(final Pageable pageable, final Map<String, Object> params, UserBean user) {

        if (params.containsKey("institution_code")) {
            params.put("institution_code", "'" + (String) params.get("institution_code") + "'");
        }
        if (PermUtils.isWorker(user)) {
            params.put("user_name", user.getUsername());
        } else if (PermUtils.isWorkerGroupLeader(user)) {
            if (!params.containsKey("institution_code")) {
                final List<InstitutionsDTO> insts = this.institutionsService.getPermInstitutions(user);
                final List<String> instCodeArray =
                        insts.stream().map(it -> "'" + it.getCode() + "'").collect(Collectors.toList());
                final String institution_code = String.join(",", instCodeArray);
                params.put("institution_code", institution_code);
            }
        }

        final String countSql = this.buildTotalSql(params);
        HnInspectionHealthTaskServiceImpl.log.info(countSql);
        final Query query1 = this.entityManager.createNativeQuery(countSql);
        query1.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result1 = query1.getResultList();
        int total = 0;
        if (result1.size() > 0) {
            total = Integer.parseInt(String.valueOf(result1.get(0).get("count")));
        }

        final String sql = this.buildQuerySql(pageable, params);
        HnInspectionHealthTaskServiceImpl.log.info(sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();
        return new PageImpl<>(result, pageable, total);
    }

    @SuppressWarnings("unchecked")
    @Override
    public Workbook exportReport(final Map<String, Object> params, UserBean user) {
        if (params.containsKey("institution_code")) {
            params.put("institution_code", "'" + (String) params.get("institution_code") + "'");
        }
        if (PermUtils.isWorker(user)) {
            params.put("user_name", user.getUsername());
        } else if (PermUtils.isWorkerGroupLeader(user)) {
            if (!params.containsKey("institution_code")) {
                final List<InstitutionsDTO> insts = this.institutionsService.getPermInstitutions(user);
                final List<String> instCodeArray =
                        insts.stream().map(it -> "'" + it.getCode() + "'").collect(Collectors.toList());
                final String institution_code = String.join(",", instCodeArray);
                params.put("institution_code", institution_code);
            }
        }
        final String sql = this.buildExportSql(params);
        HnInspectionHealthTaskServiceImpl.log.info(sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();

        final List<InspectionReportVO> list = new ArrayList<>(result.size() == 0 ? 1 : result.size());
        result.stream().map(t -> {
            final InspectionReportVO vo = new InspectionReportVO();
            vo.setStart_date_time(t.get("start_date_time"));
            vo.setInstitution_name(t.get("institution_name"));
            vo.setUser_name(t.get("user_name"));
            vo.setStatus_name(t.get("status_name"));
            vo.setActual_date_time(t.get("actual_date_time"));
            vo.setResult_text(t.get("result_text"));
            return vo;
        }).forEach(list::add);

        final ExportParams param =
                new ExportParams("定期健康巡检统计", "定期健康巡检统计", ExcelType.XSSF);
        param.setStyle(EasyPoiStylerColorImpl.class);
        return ExcelExportUtil.exportExcel(param, InspectionReportVO.class, list);
    }

    private String buildTotalSql(final Map<String, Object> params) {
        final String statement = " count(*) as count ";
        return this.buildSql(params, statement);
    }

    private String buildQuerySql(Pageable pageable, final Map<String, Object> params) {
        final String statement = " t0.id, "
                + " DATE(t0.start_date_time) AS start_date_time, "
                + " t0.institution_name, "
                + " t0.user_name, "
                + " CASE t0. STATUS "
                + "WHEN 'notStarted' THEN "
                + " '待处理' "
                + "WHEN 'inspecting' THEN "
                + " '进行中' "
                + "WHEN 'finished' THEN "
                + " '已完成' "
                + "END AS status_name, "
                + " DATE(t0.actual_date_time) AS actual_date_time, "
                + " CASE "
                + "WHEN t1.normal_item_count = t1.total_item_count THEN "
                + " '正常' "
                + "WHEN t1.normal_item_count < t1.total_item_count THEN "
                + " '异常' "
                + "END AS result_text ";
        String noPageSql = this.buildSql(params, statement);
        noPageSql += " limit " + pageable.getPageSize() + " offset " + pageable.getOffset();

        return noPageSql;
    }

    private String buildExportSql(final Map<String, Object> params) {
        final String statement = " t0.id, "
                + " DATE(t0.start_date_time) AS start_date_time, "
                + " t0.institution_name, "
                + " t0.user_name, "
                + " CASE t0. STATUS "
                + "WHEN 'notStarted' THEN "
                + " '待处理' "
                + "WHEN 'inspecting' THEN "
                + " '进行中' "
                + "WHEN 'finished' THEN "
                + " '已完成' "
                + "END AS status_name, "
                + " DATE(t0.actual_date_time) AS actual_date_time, "
                + " CASE "
                + "WHEN t1.normal_item_count = t1.total_item_count THEN "
                + " '正常' "
                + "WHEN t1.normal_item_count < t1.total_item_count THEN "
                + " '异常' "
                + "END AS result_text ";
        return this.buildSql(params, statement);
    }

    private String buildSql(final Map<String, Object> params, String statement) {
        String where = "where 1=1 ";
        if (params.containsKey("status")) {
            where += "and t0.status='" + (String) params.get("status") + "' ";
        }
        if (params.containsKey("institution_code")) {
            where += "and t0.institution_code in (" + (String) params.get("institution_code") + ") ";
        }
        if (params.containsKey("start")) {
            where += "and t0.start_date_time>='" + (String) params.get("start") + "' ";
        }
        if (params.containsKey("end")) {
            Date end = null;
            try {
                end = DateUtils.parseDate((String) params.get("end"), "yyyy-MM-dd");
            } catch (final ParseException e) {
            }
            if (null != end) {
                final Calendar c = Calendar.getInstance();
                c.setTime(end);
                c.add(Calendar.DAY_OF_MONTH, 1);
                where += "and t0.start_date_time<'" + DateFormatUtils.format(c.getTime(), "yyyy-MM-dd") + "' ";
            }
        }

        String sql = "SELECT "
                + statement
                + " FROM "
                + " neuiot_inspection_health_task_hn t0 "
                + "LEFT JOIN ( "
                + " SELECT "
                + "     t1.task_id, "
                + "     t1. STATUS, "
                + "     count(*) AS normal_item_count, "
                + "     t2.total_item_count "
                + " FROM "
                + "     neuiot_inspection_health_task_hn t0 "
                + " LEFT JOIN neuiot_inspection_health_task_item_value_hn t1 ON t0.id = t1.task_id "
                + " LEFT JOIN ( "
                + "     SELECT "
                + "         t1.task_id, "
                + "         count(*) AS total_item_count "
                + "     FROM "
                + "         neuiot_inspection_health_task_hn t0 "
                + "     LEFT JOIN neuiot_inspection_health_task_item_value_hn t1 ON t0.id = t1.task_id "
                + "     %s "
                + "     GROUP BY "
                + "         t1.task_id "
                + " ) t2 ON t0.id = t2.task_id "
                + " %s "
                + " AND t1. STATUS = 'normal' "
                + " GROUP BY "
                + "     t1.task_id, "
                + "     t1. STATUS, "
                + "     t2.total_item_count "
                + ") t1 ON t0.id = t1.task_id "
                + "     %s ";

        sql = sql.replace("%s", where);

        return sql;
    }

    @Setter
    @Getter
    static class InspectionReportVO {

        @Excel(name = "巡检日期", orderNum = "0", width = 30)
        private Object start_date_time;

        @Excel(name = "服务厅局", orderNum = "1", width = 30)
        private Object institution_name;

        @Excel(name = "巡检人", orderNum = "2", width = 30)
        private Object user_name;

        @Excel(name = "巡检状态", orderNum = "3", width = 30)
        private Object status_name;

        @Excel(name = "完成时间", orderNum = "4", width = 30)
        private Object actual_date_time;

        @Excel(name = "巡检结果", orderNum = "5", width = 30)
        private Object result_text;

    }

}
